#!/bin/bash

# MySQL连接诊断脚本
# 用于诊断MySQL连接权限和网络问题

echo "=== MySQL连接诊断脚本 ==="
echo "时间: $(date)"
echo ""

# 颜色定义
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m' # No Color

# 配置变量
MYSQL_HOST="192.168.10.102"
MYSQL_PORT="3306"
ROOT_PASSWORD="your_root_password"  # 请替换为实际密码
MAXWELL_PASSWORD="maxwell"

echo -e "${BLUE}1. 检查网络连通性${NC}"
echo "----------------------------------------"

# 检查端口连通性
echo "检查MySQL端口连通性..."
if nc -z $MYSQL_HOST $MYSQL_PORT 2>/dev/null; then
    echo -e "${GREEN}✓ MySQL端口 $MYSQL_PORT 可访问${NC}"
else
    echo -e "${RED}✗ MySQL端口 $MYSQL_PORT 不可访问${NC}"
    echo "请检查："
    echo "  - MySQL服务是否启动"
    echo "  - 防火墙设置"
    echo "  - 网络连接"
    exit 1
fi

# 检查主机连通性
echo "检查主机连通性..."
if ping -c 3 $MYSQL_HOST >/dev/null 2>&1; then
    echo -e "${GREEN}✓ 主机 $MYSQL_HOST 可达${NC}"
else
    echo -e "${RED}✗ 主机 $MYSQL_HOST 不可达${NC}"
fi

echo ""
echo -e "${BLUE}2. 检查MySQL服务状态${NC}"
echo "----------------------------------------"

# 检查MySQL服务状态
echo "检查MySQL服务状态..."
if systemctl is-active --quiet mysqld; then
    echo -e "${GREEN}✓ MySQL服务正在运行${NC}"
else
    echo -e "${RED}✗ MySQL服务未运行${NC}"
    echo "启动MySQL服务..."
    systemctl start mysqld
    sleep 3
    if systemctl is-active --quiet mysqld; then
        echo -e "${GREEN}✓ MySQL服务已启动${NC}"
    else
        echo -e "${RED}✗ MySQL服务启动失败${NC}"
        exit 1
    fi
fi

echo ""
echo -e "${BLUE}3. 测试本地MySQL连接${NC}"
echo "----------------------------------------"

# 测试本地连接
echo "测试本地MySQL连接..."
if mysql -u root -p$ROOT_PASSWORD -e "SELECT 1;" 2>/dev/null; then
    echo -e "${GREEN}✓ 本地root连接成功${NC}"
else
    echo -e "${RED}✗ 本地root连接失败${NC}"
    echo "可能原因："
    echo "  - 密码错误"
    echo "  - MySQL配置问题"
    echo "  - 权限问题"
fi

echo ""
echo -e "${BLUE}4. 检查用户权限配置${NC}"
echo "----------------------------------------"

# 检查用户权限（如果本地连接成功）
if mysql -u root -p$ROOT_PASSWORD -e "SELECT 1;" 2>/dev/null; then
    echo "检查用户权限配置..."
    
    # 检查root用户权限
    echo "Root用户权限："
    mysql -u root -p$ROOT_PASSWORD -e "SELECT user, host FROM mysql.user WHERE user='root';" 2>/dev/null
    
    # 检查maxwell用户权限
    echo "Maxwell用户权限："
    mysql -u root -p$ROOT_PASSWORD -e "SELECT user, host FROM mysql.user WHERE user='maxwell';" 2>/dev/null
    
    # 检查maxwell用户详细权限
    echo "Maxwell用户详细权限："
    mysql -u root -p$ROOT_PASSWORD -e "SHOW GRANTS FOR 'maxwell'@'%';" 2>/dev/null 2>/dev/null || \
    mysql -u root -p$ROOT_PASSWORD -e "SHOW GRANTS FOR 'maxwell'@'localhost';" 2>/dev/null || \
    echo "未找到maxwell用户权限信息"
else
    echo -e "${YELLOW}⚠ 无法检查用户权限（本地连接失败）${NC}"
fi

echo ""
echo -e "${BLUE}5. 测试远程连接${NC}"
echo "----------------------------------------"

# 测试远程root连接
echo "测试远程root连接..."
if mysql -h $MYSQL_HOST -P $MYSQL_PORT -u root -p$ROOT_PASSWORD -e "SELECT 1;" 2>/dev/null; then
    echo -e "${GREEN}✓ 远程root连接成功${NC}"
else
    echo -e "${RED}✗ 远程root连接失败${NC}"
    echo "错误信息："
    mysql -h $MYSQL_HOST -P $MYSQL_PORT -u root -p$ROOT_PASSWORD -e "SELECT 1;" 2>&1 | head -5
fi

# 测试远程maxwell连接
echo "测试远程maxwell连接..."
if mysql -h $MYSQL_HOST -P $MYSQL_PORT -u maxwell -p$MAXWELL_PASSWORD -e "SELECT 1;" 2>/dev/null; then
    echo -e "${GREEN}✓ 远程maxwell连接成功${NC}"
else
    echo -e "${RED}✗ 远程maxwell连接失败${NC}"
    echo "错误信息："
    mysql -h $MYSQL_HOST -P $MYSQL_PORT -u maxwell -p$MAXWELL_PASSWORD -e "SELECT 1;" 2>&1 | head -5
fi

echo ""
echo -e "${BLUE}6. 检查MySQL配置${NC}"
echo "----------------------------------------"

# 检查bind-address配置
echo "检查bind-address配置..."
if [ -f /etc/my.cnf ]; then
    grep -i "bind-address" /etc/my.cnf || echo "未找到bind-address配置"
elif [ -f /etc/mysql/my.cnf ]; then
    grep -i "bind-address" /etc/mysql/my.cnf || echo "未找到bind-address配置"
else
    echo "未找到MySQL配置文件"
fi

# 检查skip-networking配置
echo "检查skip-networking配置..."
if [ -f /etc/my.cnf ]; then
    grep -i "skip-networking" /etc/my.cnf || echo "未找到skip-networking配置"
elif [ -f /etc/mysql/my.cnf ]; then
    grep -i "skip-networking" /etc/mysql/my.cnf || echo "未找到skip-networking配置"
fi

echo ""
echo -e "${BLUE}7. 修复建议${NC}"
echo "----------------------------------------"

echo "如果连接失败，请尝试以下修复步骤："
echo ""
echo "1. 重置root密码："
echo "   mysql -u root -p"
echo "   ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';"
echo "   FLUSH PRIVILEGES;"
echo ""
echo "2. 创建maxwell用户并授权："
echo "   CREATE USER 'maxwell'@'%' IDENTIFIED BY 'maxwell';"
echo "   GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'maxwell'@'%';"
echo "   GRANT SELECT, INSERT, UPDATE, DELETE ON maxwell.* TO 'maxwell'@'%';"
echo "   FLUSH PRIVILEGES;"
echo ""
echo "3. 允许root远程连接："
echo "   CREATE USER 'root'@'%' IDENTIFIED BY 'password';"
echo "   GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';"
echo "   FLUSH PRIVILEGES;"
echo ""
echo "4. 检查防火墙："
echo "   firewall-cmd --permanent --add-port=3306/tcp"
echo "   firewall-cmd --reload"
echo ""
echo "5. 重启MySQL服务："
echo "   systemctl restart mysqld"

echo ""
echo "=== 诊断完成 ===" 